<?php

$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setTitle("title")->setDescription("description");

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle("Statistik Mahasiswa");

$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(4)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
$objPHPExcel->getActiveSheet()->mergeCells('A1:E1')->setCellValue('A1', 'Statistik Mahasiswa PENS Tahun Ajaran 2013/2014');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 3, 'No');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 3, 'NRP');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 3, 'Nama Mahasiwa');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, 3, 'Angkatan');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, 3, 'Keterangan');
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(25);
$objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A3:E3')->getFill()->getStartColor()->setARGB('bebebf');
$a	= 4;
$no = 1;
foreach($qmahasiswa as $row){
	$objPHPExcel->getActiveSheet()->getRowDimension($a)->setRowHeight(25);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $a, $no);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $a, $row["NRP"]);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $a, $row["NAMA"]);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $a, (($row["TGLMASUK"] == NULL || $row["TGLMASUK"] == "" || $row["TGLMASUK"] == "0000-00-00")) ? ("-") : (date("Y",strtotime($row["TGLMASUK"]))));
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $a, ($row["STATUS"] == 0) ? ("Belum Lulus") : (($row["STATUS"] == 1) ? ("Lulus") : (($row["STATUS"] == 2) ? ("DO") : ("Cuti"))));
	$no++;
	$a++;
}

$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(6)->setWidth(40);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(7)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(25);
$objPHPExcel->getActiveSheet()->getStyle('G3:H7')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('G3:H7')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('G3:H3')->getFill()->getStartColor()->setARGB('bebebf');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, 3, 'Keterangan');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, 3, 'Jumlah');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, 4, 'Cuti');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, 4, '=COUNTIF(E2:E' . $a . ',"Cuti")');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, 5, 'DO');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, 5, '=COUNTIF(E2:E' . $a . ',"DO")');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, 6, 'Belum Lulus');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, 6, '=COUNTIF(E2:E' . $a . ',"Belum Lulus")');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, 7, 'Lulus');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, 7, '=COUNTIF(E2:E' . $a . ',"Lulus")');

$dataseriesLabels = array(
	new PHPExcel_Chart_DataSeriesValues('String', '\'Statistik Mahasiswa\'!$H$3', null, 1),	//	2010
);
//	Set the X-Axis Labels
//		Datatype
//		Cell reference for data
//		Format Code
//		Number of datapoints in series
//		Data values
//		Data Marker
$xAxisTickValues = array(
	new PHPExcel_Chart_DataSeriesValues('String', '\'Statistik Mahasiswa\'!$G$4:$G$7', null, 4),	//	Q1 to Q4
);
//	Set the Data values for each data series we want to plot
//		Datatype
//		Cell reference for data
//		Format Code
//		Number of datapoints in series
//		Data values
//		Data Marker
$dataSeriesValues = array(
	new PHPExcel_Chart_DataSeriesValues('Number', '\'Statistik Mahasiswa\'!$H$4:$H$7', null, 4),
);

//	Build the dataseries
$series = new PHPExcel_Chart_DataSeries(
	PHPExcel_Chart_DataSeries::TYPE_BARCHART,		// plotType
	PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,	// plotGrouping
	range(0, count($dataSeriesValues)-1),			// plotOrder
	$dataseriesLabels,								// plotLabel
	$xAxisTickValues,								// plotCategory
	$dataSeriesValues								// plotValues
);
//	Set additional dataseries parameters
//		Make it a horizontal bar rather than a vertical column graph
$series->setPlotDirection(PHPExcel_Chart_DataSeries::DIRECTION_BAR);

//	Set the series in the plot area
$plotarea = new PHPExcel_Chart_PlotArea(null, array($series));
//	Set the chart legend
$legend = new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT, null, false);

$title = new PHPExcel_Chart_Title('Statistik Mahasiswa Pens');
$yAxisLabel = new PHPExcel_Chart_Title('');


//	Create the chart
$chart = new PHPExcel_Chart(
	'chart1',		// name
	$title,			// title
	$legend,		// legend
	$plotarea,		// plotArea
	true,			// plotVisibleOnly
	0,				// displayBlanksAs
	null,			// xAxisLabel
	$yAxisLabel		// yAxisLabel
);

//	Set the position where the chart should appear in the INDIKATOR
$chart->setTopLeftPosition('G9');
$chart->setBottomRightPosition('L17');
$objPHPExcel->getActiveSheet()->addChart($chart);

/*Worksheet Keterangan SP dan Cuti*/
$objPHPExcel->createSheet(NULL, 1);
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle("Keterangan SP");
$a = 3;
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
$objPHPExcel->getActiveSheet()->mergeCells('A1:E1')->setCellValue('A1', 'Keterangan SP Mahasiswa PENS');
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(4)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $a, 'No');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $a, 'NRP');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $a, 'Nama Mahasiwa');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $a, 'Tanggal SP');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $a, 'Keterangan');
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(25);
$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->getStartColor()->setARGB('bebebf');

$a++;
$no=1;
foreach($qsp as $row){
	$objPHPExcel->getActiveSheet()->getRowDimension($a)->setRowHeight(25);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $a, $no);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $a, $row["NRP"]);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $a, $row["NAMA"]);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $a, date("d-m-Y",strtotime($row["TANGGAL_SP"])));
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $a, strip_tags($row["KETERANGAN"]));
	$no++;
	$a++;
}

$objPHPExcel->createSheet(NULL, 2);
$objPHPExcel->setActiveSheetIndex(2);
$objPHPExcel->getActiveSheet()->setTitle("Keterangan Cuti");
$a	= 3;
//no column, no baris, isi
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
$objPHPExcel->getActiveSheet()->mergeCells('A1:F1')->setCellValue('A1', 'Keterangan Cuti Mahasiswa PENS');
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(4)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(5)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $a, 'No');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $a, 'NRP');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $a, 'Nama Mahasiwa');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $a, 'Mulai Cuti');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $a, 'Selesai Cuti');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $a, 'Keterangan');
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(25);
$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':F'.$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':F'.$a)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':F'.$a)->getFill()->getStartColor()->setARGB('bebebf');

$a++;
$no=1;
foreach($qcuti as $row){
	$objPHPExcel->getActiveSheet()->getRowDimension($a)->setRowHeight(25);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':F'.$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':F'.$a)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $a, $no);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $a, $row["NRP"]);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $a, $row["NAMA"]);
	$semester	= ($row["SEMESTER"] == 1) ? ("Ganjil") : ("Genap");
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $a, "Semester " . $semester . " " . $row["TAHUN_AJARAN"]);
	$tgl_masuk	= explode("/",$row["TAHUN_AJARAN"]);
	$tgl_masuk2 = $tgl_masuk[1] + 1;
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $a, "Semester " . $semester . " " . $tgl_masuk[1] . "/" . $tgl_masuk2);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $a, strip_tags($row["KETERANGAN"]));
	$no++;
	$a++;
}

$objPHPExcel->createSheet(NULL, 3);
$objPHPExcel->setActiveSheetIndex(3);
$objPHPExcel->getActiveSheet()->setTitle("IPS Mahasiswa");

$b = 1;
$a	= 3;
//no column, no baris, isi
foreach($qtahun_ajaran as $row){
	$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$b)->getFont()->setSize(18);
	$objPHPExcel->getActiveSheet()->mergeCells('A'.$b.':E'.$b)->setCellValue('A'.$b, 'IPS Mahasiswa PENS Tahun Ajaran ' . $row["TAHUN_AJARAN"]);
	
	// $a++;
	// $b = $a + 1;
	$b+=2;
	$a+=2;
	$objPHPExcel->getActiveSheet()->getStyle('A'.$b)->getFont()->setSize(14);
	$objPHPExcel->getActiveSheet()->mergeCells('A'.$b.':E'.$b)->setCellValue('A'.$b, 'Semester Ganjil');
	$join=array(
					array("table"=>"semester_mahasiswa","join_key"=>"ID_MAHASISWA","join_table"=>"master_mahasiswa")
				);
	$qsemester = $this->semester_mahasiswa_model->get_join_by($join,array("ID_TAHUN_AJARAN"=>$row["ID_TAHUN_AJARAN"],"SEMESTER"=>1),FALSE,array("NAMA","NRP","IPS"),"IPS DESC");
	$no=1;
	$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setWidth(5);
	$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setWidth(25);
	$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(4)->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(5)->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $a, 'No');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $a, 'NRP');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $a, 'Nama Mahasiwa');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $a, 'IPS');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $a, 'Keterangan');
	$objPHPExcel->getActiveSheet()->getRowDimension($b)->setRowHeight(25);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->getStartColor()->setARGB('bebebf');
	$a++;
	foreach($qsemester as $row2){
		$objPHPExcel->getActiveSheet()->getRowDimension($a)->setRowHeight(25);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $a, $no);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $a, $row2["NRP"]);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $a, $row2["NAMA"]);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $a, $row2["IPS"]);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $a, ($row2["IPS"] > 1.8) ? ("Naik") : ("Tidak Naik"));
		$no++;
		$a++;
		$b++;
	}
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$objPHPExcel->getActiveSheet()->mergeCells('A'.$a.':C'.$a)->setCellValue('A'.$a, 'Rata - Rata');
	if($no > 1)
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$a, '=AVERAGE(D' . ($a - $no + 1) . ':D' . ($a - 1) . ')');
	
	$b+=5;
	$a+=4;
	
	$objPHPExcel->getActiveSheet()->getStyle('A'.$b)->getFont()->setSize(14);
	$objPHPExcel->getActiveSheet()->mergeCells('A'.$b.':E'.$b)->setCellValue('A'.$b, 'Semester Genap');
	$join=array(
					array("table"=>"semester_mahasiswa","join_key"=>"ID_MAHASISWA","join_table"=>"master_mahasiswa")
				);
	$qsemester = $this->semester_mahasiswa_model->get_join_by($join,array("ID_TAHUN_AJARAN"=>$row["ID_TAHUN_AJARAN"],"SEMESTER"=>2),FALSE,array("NAMA","NRP","IPS"),"IPS DESC");
	$no=1;
	$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setWidth(5);
	$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setWidth(25);
	$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(4)->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(5)->setAutoSize(true);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $a, 'No');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $a, 'NRP');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $a, 'Nama Mahasiwa');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $a, 'IPS');
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $a, 'Keterangan');
	$objPHPExcel->getActiveSheet()->getRowDimension($b)->setRowHeight(25);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->getStartColor()->setARGB('bebebf');
	$a++;
	foreach($qsemester as $row2){
		$objPHPExcel->getActiveSheet()->getRowDimension($a)->setRowHeight(25);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
		$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $a, $no);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $a, $row2["NRP"]);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $a, $row2["NAMA"]);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $a, $row2["IPS"]);
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $a, ($row2["IPS"] > 1.8) ? ("Naik") : ("Tidak Naik"));
		$no++;
		$a++;
		$b++;
	}
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$objPHPExcel->getActiveSheet()->mergeCells('A'.$a.':C'.$a)->setCellValue('A'.$a, 'Rata - Rata');
	($no == 1) ? ($start = $a - 1) : ("");
	if($no > 1){
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$a, '=AVERAGE(D' . ($a - $no + 1) . ':D' . ($a - 1) . ')');
	$a++;
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$objPHPExcel->getActiveSheet()->mergeCells('A'.$a.':C'.$a)->setCellValue('A'.$a, 'Total Naik');
	// $objPHPExcel->getActiveSheet()->setCellValue('=COUNTIF(E' . $start . ':E' . $a . ',"Naik")');
	$a++;
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
	$objPHPExcel->getActiveSheet()->getStyle('A'.$a.':E'.$a)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
	$objPHPExcel->getActiveSheet()->mergeCells('A'.$a.':C'.$a)->setCellValue('A'.$a, 'Total Tidak Naik');
	// $objPHPExcel->getActiveSheet()->setCellValue('=COUNTIF(E' . $start . ':E' . $a . ',"Tidak Naik")');
	}
	$b = $a + 2;
	$a = $b + 2;
	
}
// Save it as an excel 2007 file
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment;filename='Cetak Statistik Mahasiswa.xlsx'");
header('Cache-Control: max-age=0');
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setIncludeCharts(TRUE);
$objWriter->save('php://output');
?>